/*******************************************************************************
 * Execute on FTCSEAP6505
 * Iterates through the defined Linked Servers and inventories SSIS and DTS
 * packages on those servers.
 */
SET IMPLICIT_TRANSACTIONS OFF
IF @@SERVERNAME <> 'FTCSEAP6505' BEGIN RAISERROR('This is not FTCSEAP6505.', 11, 0); RETURN END
USE DBA
DECLARE
	@servername		SYSNAME,
	@SQLVersion		INTEGER,
	@select9		NVARCHAR(MAX),
	@select10		NVARCHAR(MAX),
	@tsql_command	NVARCHAR(MAX)

SET @select9= REPLACE('SELECT "LINKEDSERVER" AS [server], "SSIS" AS [type], "/MSDB"+CASE f.foldername WHEN "" THEN "" ELSE "/"+f.foldername END+"/"+p.name AS name
  FROM [LINKEDSERVER].msdb.dbo.sysdtspackages90 p INNER JOIN [LINKEDSERVER].msdb.dbo.sysdtspackagefolders90 f ON (p.folderid = f.folderid)
UNION SELECT DISTINCT "LINKEDSERVER" AS [server], "DTS" AS [type], p.name FROM [LINKEDSERVER].msdb.dbo.sysdtspackages p', '"', '''')

SET @select10= REPLACE('SELECT "LINKEDSERVER" AS [server], "SSIS" AS [type], "/MSDB"+CASE f.foldername WHEN "" THEN "" ELSE "/"+f.foldername END+"/"+p.name AS name
  FROM [LINKEDSERVER].msdb.dbo.sysssispackages p INNER JOIN [LINKEDSERVER].msdb.dbo.sysssispackagefolders f ON (p.folderid = f.folderid)
UNION SELECT DISTINCT "LINKEDSERVER" AS [server], "DTS" AS [type], p.name FROM [LINKEDSERVER].msdb.dbo.sysdtspackages p', '"', '''')

BEGIN TRY
  CREATE TABLE #inventory ([server] SYSNAME, [type] VARCHAR(4), [name] VARCHAR(99))
END TRY BEGIN CATCH
  TRUNCATE TABLE #inventory
END CATCH

INSERT INTO #inventory SELECT @@SERVERNAME AS [server], 'SSIS' AS [type], '/MSDB'+CASE f.foldername WHEN '' THEN '' ELSE '/'+f.foldername END+'/'+p.name AS name
  FROM msdb.dbo.sysssispackages p INNER JOIN msdb.dbo.sysssispackagefolders f ON (p.folderid = f.folderid)
UNION SELECT DISTINCT @@SERVERNAME AS [server], 'DTS' AS [type], p.name FROM msdb.dbo.sysdtspackages p
--SELECT * FROM #inventory

DECLARE list CURSOR LOCAL FAST_FORWARD FOR
	SELECT Name, SQLVersion= CAST(LEFT(VersionSQL,CHARINDEX('.', VersionSQL)-1) AS INT)
	  FROM master.sys.servers s INNER JOIN DBA..DBA_Servers p ON (s.name = p.Server)
	  WHERE p.ServerRole = 'P' AND BehindFirewall = 0
	  ORDER BY Name
OPEN list
  FETCH NEXT FROM list INTO @servername, @SQLVersion
  WHILE @@FETCH_STATUS = 0 BEGIN
	IF @SQLVersion < 10 SET @tsql_command= REPLACE(@select9 , 'LINKEDSERVER', @servername)
	ELSE                SET @tsql_command= REPLACE(@select10, 'LINKEDSERVER', @servername)
	BEGIN TRY
	  PRINT @servername
--	  PRINT @tsql_command
	  INSERT INTO #inventory EXEC (@tsql_command)
	END TRY BEGIN CATCH END CATCH
	SET @tsql_command= ''
	FETCH NEXT FROM list INTO @servername, @SQLVersion
  END
CLOSE list
DEALLOCATE list
GO

WITH s AS (SELECT DISTINCT [server] FROM #inventory)
SELECT
	s.[server],
	DTS	= (SELECT COUNT(*) FROM #inventory WHERE [server] = s.[server] AND [type] = 'DTS'),
	SSIS= (SELECT COUNT(*) FROM #inventory WHERE [server] = s.[server] AND [type] = 'SSIS')
  FROM s ORDER BY [server]
GO

SELECT * FROM #inventory ORDER BY 1,2,3
GO